﻿use banktest;
go

--1）编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。
go
create view V_Acid(卡号,身份证,姓名,余额)
as
select b.CardNo,a.AccountCode,RealName,CardMoney from BankCard b join AccountInfo a on b.AccountId=a.AccountId
go
select * from V_Acid

--2）行转列常用做法: group by + sum(case when) /+count(case when)  数据分析+ over (paritition by, order by)

​--   提示：  pivot

select a.RealName 姓名,a.AccountId  编号,
sum(case when a.RealName='刘备' then a.AccountId else 1 end) 'asc-14',
sum(case when a.RealName='张飞' then a.AccountId else 1 end) 'xzy-254',
sum(case when a.RealName='关羽' then a.AccountId else 1 end) 'mnb-215'
from BankCard b join AccountInfo a on b.AccountId=a.AccountId
group by a.RealName,a.AccountId

select RealName,AccountPhone from AccountInfo where AccountId='1'
union
select RealName,AccountPhone from AccountInfo where AccountId='2'
union
select RealName,AccountPhone from AccountInfo where AccountId='3'